/**************************************************************************
* This macro adds actuarial information from schedule B or SB to the 
* data from the main 5500 form for DB plan records
/*************************************************************************/

%macro addschbdat(yyyy);

data temp&yyyy;
  set blue.pens&yyyy;
  where DB=/*redacted*/;
run;

/*Double check for filingid dups*/
proc sort data = temp&yyyy out = temp nodupkey;
  by opr_ein opr_pn filing_id_char;
run;

proc sql;
	create table db&yyyy as 
	select a.*, b.plan_assets, b.plan_liab, b.actives_liab,
	b.rtd_liab, b.asset_return, b.cost_method_code, b.xpct_liab_incr,
	b.plan_R_age, b.actives_cnt, b.rtd_cnt, b.term_cnt,
	a.cash_bal*a.plan_year_end as yr_cash_bal,
	a.frozen*a.plan_year_end as yr_freeze
	from temp&yyyy as a left join 
	blue.schb_p&yyyy as b
	on a.filing_id_char = b.filing_id_char; 
quit;

/*Delete plan filing duplicates*/
proc sort data = db&yyyy nodupkey;
  by opr_ein opr_pn;
run;

/*Obtain missing ptcp count from actuarial attachment if missing from main form*/
data db&yyyy;
  set db&yyyy;
  if yr_cash_bal=/*redacted*/ then yr_cash_bal=.;
  if (DB_partcp=. & actives_cnt^=.) then DB_partcp=actives_cnt;      
  if (adj_DB_partcp=. & actives_cnt^=.) then adj_DB_partcp=actives_cnt;
run;

%mend;

%macro addschbdat0914(yyyy,yy);

data temp&yyyy;
  set blue.pens&yyyy;
  where DB=/*redacted*/;
run;

/*Double check for filingid dups*/
proc sort data = temp&yyyy out = temp nodupkey;
  by opr_ein opr_pn filing_id_char;
run;

proc sql;
	create table db&yyyy as 
	select a.*, b.plan_assets, b.plan_liab, b.actives_liab,
	b.rtd_liab, b.asset_return, b.cost_method_code, b.xpct_liab_incr,
	b.plan_R_age, b.actives_cnt, b.rtd_cnt, b.term_cnt,
	a.cash_bal*a.plan_year_end as yr_cash_bal,
	a.frozen*a.plan_year_end as yr_freeze
	from temp&yyyy as a left join 
	blue.schsb_p&yyyy as b
	on a.filing_id_char = b.filing_id_char; 
quit;

/*Delete plan filing duplicates*/
proc sort data = db&yyyy nodupkey;
  by opr_ein opr_pn;
run;

/*Obtain missing ptcp count from actuarial attachment if missing from main form*/
data db&yyyy;
  set db&yyyy;
  if yr_cash_bal=/*redacted*/ then yr_cash_bal=.;
  if (DB_partcp=. & actives_cnt^=.) then DB_partcp=actives_cnt;      
  if (adj_DB_partcp=. & actives_cnt^=.) then adj_DB_partcp=actives_cnt;
run;

%mend;


